# Copyright (c) 2024, hzmes and contributors
# For license information, please see license.txt

import frappe
from frappe.utils import cstr, flt, cint, get_first_day, get_last_day
from frappe.model.document import Document
from datetime import date
from hzplm.hz_plm.doctype.server_config.server_config import get_fa_deprdistribute_from_erp

class ExpenseAllocationDoc(Document):
	# 员工费用---获取工时计算calc_allocation
	@frappe.whitelist()
	def calc_allocation(self):
		# 2025-05-08优化: 获取单据日期作为工时筛选改为根据单据中的年和月去筛选工时记录
		d = date(cint(self.year), cint(self.month), 1).strftime("%Y-%m-%d")
		if self.expense_claim_type == '折旧费' or self.expense_claim_type == '长期待摊费用' or self.expense_claim_type == '打样折旧费':
			frappe.throw('当前费用类型不适用员工类计算规则!')
		else:
			for i in self.allocation_item:	# 遍历明细行
				employee = frappe.db.get_value('Employee', {'custom_empno': i.empno}, ['name'])	# 根据工号empno找到员工name
				if employee:
					first_day = get_first_day(d)
					last_day = get_last_day(d)
					# 按指定日期范围指定员工group by 项目总工时:
					sql_project_total = f"""
						SELECT tb2.project, sum(tb2.hours) as total_hours FROM `tabTimesheet` tb1 join  `tabTimesheet Detail` tb2 on tb1.`name` = tb2.parent
						WHERE tb1.employee = '{employee}' AND tb2.custom_from_date >= '{first_day}' AND tb2.custom_from_date <= '{last_day}' AND tb1.docstatus=1
						GROUP BY tb2.project
					"""
					# 按指定日期范围指定员工总工时:
					sql_emp_total = f"""
						SELECT sum(tb2.hours) as total_hours FROM `tabTimesheet` tb1 join  `tabTimesheet Detail` tb2 on tb1.`name` = tb2.parent
						WHERE tb1.employee = '{employee}' AND tb2.custom_from_date >= '{first_day}' AND tb2.custom_from_date <= '{last_day}' AND tb1.docstatus=1
					"""
					i.from_employee = employee	# 赋值员工name
					# 计算该员工的总报工工时
					# total_hours = frappe.db.get_all('Timesheet', filters = {'employee': employee, '`tabTimesheet Detail`.custom_from_date': ['>=', get_first_day(self.date)]}, fields=['sum(`tabTimesheet Detail`.hours) as total_hours', '`tabTimesheet Detail`.is_billable'], group_by='`tabTimesheet Detail`.is_billable')
					total_hours = frappe.db.sql(sql_emp_total, as_dict=True)
					# group by 查询该员工的项目分组工时
					# 2025-02-25特别提醒: 通过明细表单据字段工时来group by
					# projects_hours = frappe.db.get_all('Timesheet', filters = {'employee': employee}, fields=['`tabTimesheet Detail`.project', 'sum(`tabTimesheet Detail`.hours) as total_hours'], group_by='`tabTimesheet Detail`.project')
					projects_hours = frappe.db.sql(sql_project_total, as_dict=True)
					# 根据该员工的项目分组工时组合和分摊
					if len(total_hours) > 0 and flt(total_hours[0]["total_hours"]) > 0:
						i.total_hours = total_hours[0]["total_hours"]
						hours_price = i.amount / total_hours[0]["total_hours"]	# 计算工时单价
						project_hours = ''
						allocation_amount_sum = 0
						for array_item in projects_hours:
							# 分配后金额求和
							allocation_amount_sum = allocation_amount_sum + flt(array_item["total_hours"] * hours_price, 2)
						if i.amount == allocation_amount_sum:	# 刚好分配完一分不差
							for array_item in projects_hours:
								# print(i.employee_name)
								allocation_amount_sum = allocation_amount_sum + flt(array_item["total_hours"] * hours_price, 2)
								# print('***')
								project_hours = project_hours + array_item["project"] + '/' + cstr(array_item["total_hours"]) + '/' + cstr(flt(array_item["total_hours"] * hours_price, 2)) + ','
							i.allocation_detail = project_hours
							i.hour_price = hours_price
						else:	# 分配后求和有尾差
							dif_amount = i.amount - allocation_amount_sum
							last_index = len(projects_hours) -1
							for index, array_item in enumerate(projects_hours):
								if index < last_index:
									project_hours = project_hours + array_item["project"] + '/' + cstr(array_item["total_hours"]) + '/' + cstr(flt(array_item["total_hours"] * hours_price, 2)) + ','
								if index == last_index:	# 在最后一个元素时把尾差加上去
									project_hours = project_hours + array_item["project"] + '/' + cstr(array_item["total_hours"]) + '/' + cstr(flt(array_item["total_hours"] * hours_price + dif_amount, 2)) + ','
							i.allocation_detail = project_hours
							i.hour_price = hours_price
				else:
					frappe.msgprint(f'{i.empno}不能匹配员工名册,请同步员工花名册!')
			self.save()
			self.reload()

	# 非员工费用---获取工时计算calc_allocation_project, 比如长期待摊费用和折旧费, 只根据总金额分摊到项目即可
	@frappe.whitelist()
	def calc_allocation_project(self):
		# 2025-05-08优化: 获取单据日期作为工时筛选改为根据单据中的年和月去筛选工时记录
		d = date(cint(self.year), cint(self.month), 1).strftime("%Y-%m-%d")
		first_day = get_first_day(d)
		last_day = get_last_day(d)
		# 按指定日期范围总工时:
		sql_total = f"""
			SELECT sum(tb2.hours) as total_hours FROM `tabTimesheet` tb1 join  `tabTimesheet Detail` tb2 on tb1.`name` = tb2.parent
			WHERE tb2.custom_from_date >= '{first_day}' AND tb2.custom_from_date <= '{last_day}' AND tb1.docstatus=1
		"""
		total_hours = frappe.db.sql(sql_total, as_dict=True)					
		# 按指定日期范围group by 项目总工时:
		sql_project_total = f"""
			SELECT tb2.project, sum(tb2.hours) as total_hours FROM `tabTimesheet` tb1 join  `tabTimesheet Detail` tb2 on tb1.`name` = tb2.parent
			WHERE tb2.custom_from_date >= '{first_day}' AND tb2.custom_from_date <= '{last_day}' AND tb1.docstatus=1
			GROUP BY tb2.project
		"""
		projects_hours = frappe.db.sql(sql_project_total, as_dict=True)
		# 计算前先清零
		self.expense_project_item = []
		if self.expense_claim_type == '折旧费':
			if self.total_amount > 0:
				total_amount = 0
				for row in self.fa_allocation_item:
					total_amount = total_amount + flt(row.amount)
				if self.total_amount == total_amount:
					if len(total_hours) > 0 and flt(total_hours[0]["total_hours"]) > 0:
						allocation_amount_sum = 0
						hours_price = self.total_amount / total_hours[0]["total_hours"]	# 计算工时单价
						for array_item in projects_hours:
							allocation_amount_sum = allocation_amount_sum + flt(array_item["total_hours"] * (self.total_amount / total_hours[0]["total_hours"]), 2)
						if allocation_amount_sum == self.total_amount:	# 如果分配后加总没有尾差
							for array_item in projects_hours:
								self.append('expense_project_item', {'project': array_item["project"], 'hours': array_item["total_hours"], 'total_hours': total_hours[0]["total_hours"], 'amount': flt(array_item["total_hours"] * (self.total_amount / total_hours[0]["total_hours"]), 2) })
						else:	# 如果分配后加总有尾差
							dif_amount = allocation_amount_sum - self.total_amount	#求尾差
							last_index = len(projects_hours) -1
							for index, array_item in enumerate(projects_hours):
								if index < last_index:
									self.append('expense_project_item', {'project': array_item["project"], 'hours': array_item["total_hours"], 'total_hours': total_hours[0]["total_hours"], 'amount': flt(array_item["total_hours"] * (self.total_amount / total_hours[0]["total_hours"]), 2) })
								if index == last_index:	# 在最后一个元素时把尾差加上去
									self.append('expense_project_item', {'project': array_item["project"], 'hours': array_item["total_hours"], 'total_hours': total_hours[0]["total_hours"], 'amount': (dif_amount + flt(array_item["total_hours"] * (self.total_amount / total_hours[0]["total_hours"]), 2)) })
						self.save()
						self.reload()
				else:frappe.throw('金额合计与折旧明细加总不等,请核查再计算分摊!')
			else:
				frappe.throw('金额合计为0无法计算分摊!')
		if self.expense_claim_type == '长期待摊费用' or self.expense_claim_type == '打样折旧费':
			if self.total_amount > 0:
				total_amount = 0
				for row in self.deferred_expenses:
					# 玄乎: 非得要flt一下否则会无端生成小数尾差
					total_amount = frappe.utils.flt(total_amount + row.period_amount, 2)
				if self.total_amount == total_amount:
					if len(total_hours) > 0 and flt(total_hours[0]["total_hours"]) > 0:
						allocation_amount_sum = 0
						hours_price = self.total_amount / total_hours[0]["total_hours"]	# 计算工时单价
						for array_item in projects_hours:
							allocation_amount_sum = allocation_amount_sum + flt(array_item["total_hours"] * (self.total_amount / total_hours[0]["total_hours"]), 2)
						if allocation_amount_sum == self.total_amount:	# 如果分配后加总没有尾差
							for array_item in projects_hours:
								self.append('expense_project_item', {'project': array_item["project"], 'hours': array_item["total_hours"], 'total_hours': total_hours[0]["total_hours"], 'amount': flt(array_item["total_hours"] * (self.total_amount / total_hours[0]["total_hours"]), 2) })
						else:	# 如果分配后加总有尾差
							dif_amount = allocation_amount_sum - self.total_amount	#求尾差
							last_index = len(projects_hours) -1
							for index, array_item in enumerate(projects_hours):
								if index < last_index:
									self.append('expense_project_item', {'project': array_item["project"], 'hours': array_item["total_hours"], 'total_hours': total_hours[0]["total_hours"], 'amount': flt(array_item["total_hours"] * (self.total_amount / total_hours[0]["total_hours"]), 2) })
								if index == last_index:	# 在最后一个元素时把尾差加上去
									self.append('expense_project_item', {'project': array_item["project"], 'hours': array_item["total_hours"], 'total_hours': total_hours[0]["total_hours"], 'amount': (dif_amount + flt(array_item["total_hours"] * (self.total_amount / total_hours[0]["total_hours"]), 2)) })
						self.save()
						self.reload()
				else:frappe.throw('金额合计与长期待摊费用明细加总不等,请核查再计算分摊!')
			else:
				frappe.throw('金额合计为0无法计算分摊!')
		if self.expense_claim_type != '折旧费' and self.expense_claim_type != '长期待摊费用' and self.expense_claim_type != '打样折旧费':
			frappe.throw('当前费用类型不适用非员工类计算规则!')

	# 根据底稿生成费用分摊doc
	@frappe.whitelist()
	def allocation(self):
		# allocation_item或expense_project_item有行记录允进行分摊
		if len(self.allocation_item) > 0 or len(self.expense_project_item) > 0:
			if self.expense_claim_type != '折旧费' and self.expense_claim_type != '长期待摊费用':
				if frappe.db.exists("Allocation Item", {"parent": self.name, "from_employee": ["=", None]}):
					row = frappe.db.exists("Allocation Item", {"parent": self.name, "from_employee": ["=", None]})
					idx = frappe.db.get_value('Allocation Item', row, "idx")
					frappe.throw(f"第{idx}行员工档案不匹配!")
				if frappe.db.exists("Allocation Item", {"parent": self.name, "total_hours": ["=", 0]}):
					row = frappe.db.exists("Allocation Item", {"parent": self.name, "total_hours": ["=", 0]})
					idx = frappe.db.get_value('Allocation Item', row, "idx")
					frappe.throw(f"第{idx}行总工时为0!")
					
				if frappe.db.exists("Allocation Item", {"parent": self.name, "allocation_detail": ["=", None]}): 
					row = frappe.db.exists("Allocation Item", {"parent": self.name, "allocation_detail": ["=", None]})
					idx = frappe.db.get_value('Allocation Item', row, "idx")
					frappe.throw(f"第{idx}行分摊明细为空!")
				# 以下
				diff_rows = []
				for i in self.allocation_item:	# 遍历明细行
					emp_total_amount = 0	# 定义临时变量
					for p in i.allocation_detail.split(','):	# 把表体行的分摊明细转换为列表
						if len(p)>0:
							emp_total_amount = emp_total_amount + frappe.utils.flt(p.split('/')[2], 2)
					if emp_total_amount == i.amount:	# 这行没有尾差
						pass
					else:	# 这行有尾差
						diff = frappe.utils.flt(emp_total_amount - i.amount, 2)	# 求出尾差
						if diff>=0.01 or diff<=-0.01:	# 尾差在1分钱或以上
							row = i.employee_name + '*' + frappe.utils.cstr(diff)
							diff_rows.append(row)
				if len(diff_rows)>0:
					frappe.throw(f"有尾差行!{diff_rows}")

				else:
					for i in self.allocation_item:	# 遍历明细行
						employee = frappe.db.get_value('Employee', {'custom_empno': i.empno}, ['name'])	# 根据工号empno找到员工name
						if employee and i.total_hours > 0 and len(i.allocation_detail) > 0:	# 员工有效and总工时大于0and分摊明细不为空
							for p in i.allocation_detail.split(','):	# 把分摊明细转换为列表
								if len(p)>0:
									print(flt(p.split('/')[2], 2) * self.reverse)
									doc = frappe.new_doc('Expense Allocation')
									doc.date = self.date
									doc.expense_claim_type = self.expense_claim_type
									doc.year = self.year
									doc.month = self.month
									doc.employee = employee
									doc.project = p.split('/')[0]	# 转换为列表:项目编号
									doc.amount = flt(p.split('/')[2], 2) * self.reverse	# 转换为列表:分摊金额, 取消cint取整
									doc.from_doc = self.name + '_' + cstr(i.idx)
									doc.fsourcebill = '凭证冲销' if self.reverse == -1 else '折旧调整单' if self.expense_claim_type == '打样折旧费' else '手工录入'
									doc.fsystem = '研发系统'
									doc.explanation = self.explanation
									doc.custom_from_doc_name = self.name	# 2025-05-08优化: 把源单号存起来
									doc.insert()	# save
								else: print('NG')
					self.finished_allocation = 'Yes'
					self.save()
					self.reload()
			if self.expense_claim_type == '折旧费' or self.expense_claim_type == '长期待摊费用' or self.expense_claim_type == '打样折旧费':
				for i in self.expense_project_item:	# 遍历明细行
					doc = frappe.new_doc('Expense Allocation')
					doc.date = self.date
					doc.expense_claim_type = self.expense_claim_type
					doc.year = self.year
					doc.month = self.month
					doc.project = i.project
					doc.amount = i.amount
					doc.from_doc = self.name + '_' + cstr(i.idx)
					doc.fsourcebill = '凭证冲销' if self.reverse == -1 else '折旧调整单' if self.expense_claim_type == '打样折旧费' else '手工录入'
					doc.fsystem = '研发系统'
					doc.explanation = self.explanation
					doc.custom_from_doc_name = self.name	# 2025-05-08优化: 把源单号存起来
					doc.insert()	# save
				self.finished_allocation = 'Yes'
				self.save()
				self.reload()
		else:frappe.throw('没有明细行可生成费用分摊!')
		
	@frappe.whitelist()
	def reverse_myself(self):
		if frappe.db.exists({"doctype": self.doctype, "from_doc": self.name}):
			frappe.throw(f'已经有冲销单!{frappe.db.exists({"doctype": self.doctype, "from_doc": self.name})}')
		else:
			new_doc = frappe.copy_doc(self, ignore_no_copy=True)
			new_doc.reverse = -1
			new_doc.custom_reverse_sign = "是"
			new_doc.from_doc = self.name
			new_doc.finished_allocation = 'No'
			new_doc.insert(ignore_permissions=True)
			return new_doc

	@frappe.whitelist()
	def get_fa_deprdistribute(self):
		result = get_fa_deprdistribute_from_erp(fyear=self.year, fperiod=self.month)
		for item in result:
			if item[0] == "折旧费用":
				self.append("fa_allocation_item", {"fa_cardnumber": item[1], "fa_card_name": item[2], "amount": item[3]})
		self.save()
		self.reload()


# 查询指定费用类型在日期范围内的单据号
@frappe.whitelist()
@frappe.validate_and_sanitize_search_inputs
def expense_allocation_doc_query(doctype, txt, searchfield, start, page_len, filters):
	return frappe.db.get_all(
		"Expense Allocation Doc",
		limit_start=start,
		limit_page_length=page_len,
		filters={
			"year": filters.get("year"),
			"fperiod": filters.get("fperiod"),
			"expense_claim_type": filters.get("expense_claim_type"),
			"name": ("like", "%%%s%%" % txt),
		},
		as_list=1,
	)
